import pymssql
import config

server = '172.16.3.19'
# server = '192.168.66.100'
user = 'sa'
password = '*#zw8'
database = 'TOP_ERP_HLJU'

try:

    # 打开数据库连接（ip/数据库用户名/登录密码/数据库名）
    db = pymssql.connect(server=server, user=user, password=password, database=database)
    print("数据库连接成功")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
except pymssql.Error as e:
    print("数据库连接失败：" + str(e))

'''查询视图时添加是否是单双周字段 IsSDWeek   2024.5.6   SPF'''


def init_course_task(deptId, year, term):
    task_course = []
    if (deptId == ''):
        sql = ''' select VB_Score_Manage.Sno, VB_Score_Manage.CourseID,VB_Score_Manage.coursename,VB_Score_Manage.Teacher,VB_Score_Manage.TutorName,VB_Score_Manage.Class,VB_SelectCourse_Base.GraduateID,WeekStart,WeekEnd,WeekHour,VB_Score_Manage.StudyHour,VB_Score_Manage.IsSDWeek 
        from VB_Score_Manage left Join VB_SelectCourse_Base on VB_SelectCourse_Base.Sno = VB_Score_Manage.Sno 
        where VB_Score_Manage.year=%s and VB_Score_Manage.term=%s and WeekStart is not null and WeekEnd is not null and
        VB_Score_Manage.IsAutoSchd=1 and VB_SelectCourse_Base.GraduateID is not null '''
    else:
        sql = f''' select VB_Score_Manage.Sno, VB_Score_Manage.CourseID,VB_Score_Manage.coursename,VB_Score_Manage.Teacher,VB_Score_Manage.TutorName,VB_Score_Manage.Class,VB_SelectCourse_Base.GraduateID,WeekStart,WeekEnd,WeekHour,VB_Score_Manage.StudyHour,VB_Score_Manage.IsSDWeek 
        from VB_Score_Manage left Join VB_SelectCourse_Base on VB_SelectCourse_Base.Sno = VB_Score_Manage.Sno 
        where VB_Score_Manage.year=%s and VB_Score_Manage.term=%s and WeekStart is not null and WeekEnd is not null and
        VB_Score_Manage.IsAutoSchd=1 and VB_Score_Manage.deptid in({deptId}) and VB_SelectCourse_Base.GraduateID is not null '''
    try:
        cursor.execute(sql, (year, term))
        results_cou = cursor.fetchall()

        # 处理初始数据（转换为带学生的教学计划）
        for cou in results_cou:
            task_found = False
            for task in task_course:
                if task[0] == str(cou[0]):
                    task[6].append(cou[6])
                    task_found = True
                    break
            if not task_found:
                task_course.append(
                    [str(cou[0]), cou[1], cou[2], cou[3], cou[4], cou[5], [cou[6]], cou[7], cou[8], cou[9], cou[10],
                     cou[11]])
    except Exception as e:
        print(e)
    return task_course


def init_sel_students(deptId, year, term):
    sel_stu_info = []
    if (deptId == ''):
        sql = ''' select distinct GraduateID from VB_SelectCourse_Base where VB_SelectCourse_Base.GraduateID is not null and Sno in (select VB_Score_Manage.Sno
        from VB_Score_Manage where VB_Score_Manage.year=%s and VB_Score_Manage.term=%s and WeekStart is not null and WeekEnd is not null and 
        VB_Score_Manage.IsAutoSchd=1)'''
    else:
        sql = f''' select distinct GraduateID from VB_SelectCourse_Base where VB_SelectCourse_Base.GraduateID is not null and Sno in (select VB_Score_Manage.Sno
        from VB_Score_Manage where VB_Score_Manage.year=%s and VB_Score_Manage.term=%s and WeekStart is not null and WeekEnd is not null and 
        VB_Score_Manage.IsAutoSchd=1 and VB_Score_Manage.deptid in({deptId})) '''
    try:
        cursor.execute(sql, (year, term))
        results_stu = cursor.fetchall()
        for stu in results_stu:
            sel_stu_info.append(stu)
    except Exception as e:
        print(e)
    return sel_stu_info


def init_sel_teachers(deptId, year, term):
    sel_tea_info = []
    if (deptId == ''):
        sql = ''' select distinct Teacher from VB_SelectCourse_Base where VB_SelectCourse_Base.GraduateID is not null and Sno in (select VB_Score_Manage.Sno
        from VB_Score_Manage where VB_Score_Manage.year=%s and VB_Score_Manage.term=%s and WeekStart is not null and WeekEnd is not null and 
        VB_Score_Manage.IsAutoSchd=1)'''
    else:
        sql = f''' select distinct Teacher from VB_SelectCourse_Base where VB_SelectCourse_Base.GraduateID is not null and Sno in (select VB_Score_Manage.Sno
        from VB_Score_Manage where VB_Score_Manage.year=%s and VB_Score_Manage.term=%s and WeekStart is not null and WeekEnd is not null and 
        VB_Score_Manage.IsAutoSchd=1 and VB_Score_Manage.deptid in({deptId})) '''
    try:
        cursor.execute(sql, (year, term))
        results_tea = cursor.fetchall()
        for tea in results_tea:
            sel_tea_info.append(tea)
    except Exception as e:
        print(e)
    return sel_tea_info


def init_classrooms():
    classroom_info = []
    # sql = "select ClassRoomID,PeopleNumber from VB_ClassRoom where ClassTypeName in ( '普通教室')"
    # 查询可用教室（之前是所有教室都可用）
    sql = '''SELECT ClassRoomID,PeopleNumber,BuildingID FROM VB_ClassRoom WHERE ClassTypeName in ('一般教室','多媒体') '''
    try:
        cursor.execute(sql)
        results_clr = cursor.fetchall()
        for clr in results_clr:
            classroom_info.append(clr)  # 将教室信息添加到classroom_info列表中
            # classroom_info = [[26，150][74,40],[2,80].......]
    except Exception as e:
        print(e)
    return classroom_info


# 教学楼信息
def init_building():
    building_info = []
    sql = '''SELECT BuildingID FROM dbo.nTB_Building_info WHERE District = 0'''
    try:
        cursor.execute(sql)
        result_clr = cursor.fetchall()
        for clr in result_clr:
            building_info.append(clr)
    except Exception as e:
        print(e)
    return building_info


# 教学楼距离表
def init_buildingDistance():
    buildingDistance_info = []
    sql = '''SELECT * FROM dbo.nTB_Buildings_Distance'''
    try:
        cursor.execute(sql)
        result_clr = cursor.fetchall()
        for clr in result_clr:
            buildingDistance_info.append(clr)
    except Exception as e:
        print(e)
    return buildingDistance_info


# 导入数据到数据库中
def InsertBatchToDB(result):
    del_sql = "TRUNCATE TABLE dbo.nTB_ArrClassPreNewAutoSd_info"
    int_sql = "insert into dbo.nTB_ArrClassPreNewAutoSd_info values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    try:
        cursor.execute(del_sql)
        cursor.executemany(int_sql, result)
        db.commit()
    except Exception as e:
        print(e)
        db.rollback()
    # finally:
    #     db.close()


# 中间状态表更新
def UpdateSchedulingStatus_start(t, startTime, Status, ArrUser, TotalCount):
    del_sql = "TRUNCATE TABLE dbo.nTB_ArrClassPreNewAutoSdStatus_info"
    insert_sql = "insert into dbo.nTB_ArrClassPreNewAutoSdStatus_info (Time,CurrentCount,Status,ArrUser,TotalCount) values (%s,%s,%s,%s,%s)"
    try:
        cursor.execute(del_sql)
        cursor.execute(insert_sql, (startTime, t + 1, Status, ArrUser, TotalCount))
        db.commit()
    except Exception as e:
        print(e)
        db.rollback()


def UpdateSchedulingStatus_end(t, startTime, Status, ArrUser, endTime, TotalCount):
    del_sql = "TRUNCATE TABLE dbo.nTB_ArrClassPreNewAutoSdStatus_info"
    insert_sql = "insert into dbo.nTB_ArrClassPreNewAutoSdStatus_info values (%s,%s,%s,%s,%s,%s)"
    try:
        cursor.execute(del_sql)
        cursor.execute(insert_sql, (startTime, (t + 1), Status, ArrUser, endTime, TotalCount))
        db.commit()
    except Exception as e:
        print(e)
        db.rollback()
